﻿
//----------------------------------------------------------------------
// <copyright file="DataBase.cs" company="O-RID">
// Copyright (c)2010 O-RID Co.,Ltd.
// </copyright>
// <author>lidawei</author>
// <summary> 
// 作成日： 2011/10/18
// </summary>
//----------------------------------------------------------------------



namespace Orid.Management.SOHO.Book.Service
{
    using System;
    using System.Data.SqlClient;
    using System.Text;
    using System.Configuration;
    using System.Collections.Generic;
    using System.Data;

    /// <summary>
    /// Book数据库访问类
    /// 供WCF使用
    /// </summary>
    public class DataBase : IDisposable
    {


        /// <summary>
        /// Initializes a new instance of the <see cref="DataBase"/> class.
        /// </summary>
        public DataBase()
        {
            connection = new SqlConnection(ConfigurationManager.ConnectionStrings["DBConnStr"].ConnectionString);
            connection.Open();
        }


        /// <summary>
        /// Adds the schedule.
        /// </summary>
        /// <param name="operatorID">The operator ID.</param>
        /// <param name="dateFrom">The date from.</param>
        /// <param name="dateTo">The date to.</param>
        /// <returns></returns>
        public static bool AddSchedule(string operatorID,DateTime dateFrom,DateTime dateTo)
        {
            using (DataBase db = new DataBase())
            {
                
                db.BeginTransaction();
 
                if (db.ScheduleDateExists(operatorID, dateFrom,dateTo))
                {
                    db.DeleteSchedule(operatorID, dateFrom, dateTo);
                }

                bool retbool = db.InsertSchedule(operatorID, dateFrom, dateTo);

                db.CommitTransaction();

                return retbool;
            }
        }


        /// <summary>
        /// Removes the schedule.
        /// </summary>
        /// <param name="operatorID">The operator ID.</param>
        /// <param name="dateFrom">The date from.</param>
        /// <param name="dateTo">The date to.</param>
        /// <returns></returns>
        public static bool RemoveSchedule(string operatorID,DateTime dateFrom, DateTime dateTo)
        {
            using (DataBase db = new DataBase( ))
            {
                db.BeginTransaction();
                bool success = db.DeleteSchedule(operatorID,  dateFrom,   dateTo);
                db.CommitTransaction();
                return success;
            }
        }


        /// <summary>
        /// Gets the current book setting.
        /// </summary>
        /// <returns></returns>
        public static  List<BookSetting> GetCurrentBookSetting()
        {
            using (DataBase db = new DataBase())
            {
                db.BeginTransaction();
                List<BookSetting> bookSettings = db.GetBookSetting();
                db.CommitTransaction();
                return bookSettings;
            }
        }


        /// <summary>
        /// Gets the book config.
        /// </summary>
        /// <returns></returns>
        public static int GetBookConfig()
        {
             using (DataBase db = new DataBase())
            {
                db.BeginTransaction();
                int bookDuration  = db.GetBookConfigII();
                db.CommitTransaction();
                return bookDuration;
            }
        }


        /// <summary>
        /// Gets the book records.
        /// </summary>
        /// <param name="operatorID">The operator  ID.</param>
        /// <param name="bookDate"></param>
        /// <returns></returns>
        public static List<BookRecord>  GetBookList(string operatorID, DateTime bookDate)
        {
            using (DataBase db = new DataBase())
            {
                db.BeginTransaction();
                List<BookRecord> bookRecords = db.GetBookListII(operatorID, bookDate);
                db.CommitTransaction();
                return bookRecords;
            }
        }


        /// <summary>
        /// Gets the book list.
        /// </summary>
        /// <param name="operatorID">The operator ID.</param>
        /// <param name="bookDateFrom">The book date from.</param>
        /// <param name="bookDateTo">The book date to.</param>
        /// <returns></returns>
        public static List<BookRecord> GetBookList(string operatorID, DateTime bookDateFrom,DateTime bookDateTo)
        {
            using (DataBase db = new DataBase())
            {
                db.BeginTransaction();
                List<BookRecord> bookRecords = db.GetBookListII(operatorID, bookDateFrom,bookDateTo);
                db.CommitTransaction();
                return bookRecords;
            }
        }


        /// <summary>
        /// Gets the next work time.
        /// </summary>
        /// <param name="operatorID">The operator ID.</param>
        /// <param name="bookDate">The book date.</param>
        /// <returns></returns>
        public static BookRecord GetNextWorkTime(string operatorID,DateTime bookDate)
        {
            using (DataBase db = new DataBase())
            {
                db.BeginTransaction();
                BookRecord  bookRecord  = db.GetNextWorkTimeII(operatorID, bookDate );
                db.CommitTransaction();
                return bookRecord;
            }
        }


        /// <summary>
        /// Gets the book days.
        /// </summary>
        /// <param name="operatorID">The operator ID.</param>
        /// <param name="dateFrom">The date from.</param>
        /// <param name="dateTo">The date to.</param>
        /// <returns></returns>
        public static List<DateTime> GetBookDays(string operatorID, DateTime dateFrom, DateTime dateTo)
        {
            using (DataBase db = new DataBase())
            {
                db.BeginTransaction();
                List<DateTime> bookDays = db.GetBookDaysII(operatorID, dateFrom, dateTo);
                db.CommitTransaction();
                return bookDays;
            }
        }


        /// <summary>
        /// Adds the login action.
        /// </summary>
        /// <param name="operatorID">The operator ID.</param>
        /// <returns></returns>
        public static bool AddLoginAction(string operatorID)
        {
            using (DataBase db = new DataBase())
            {
                db.BeginTransaction();
                bool bAction = db.InsertOperatorAction(operatorID, "Login");
                db.CommitTransaction();
                return bAction;
            }
        }


        /// <summary>
        /// Adds the logout action.
        /// </summary>
        /// <param name="operatorID">The operator ID.</param>
        /// <returns></returns>
        public static bool AddLogoutAction(string operatorID)
        {
            using (DataBase db = new DataBase())
            {
                db.BeginTransaction();
                bool bAction = db.InsertOperatorAction(operatorID, "Logout");
                db.CommitTransaction();
                return bAction;
            }
        }


        /// <summary>
        /// Schedules the date exists.
        /// </summary>
        /// <param name="operatorID"></param>
        /// <param name="dateFrom">The date from.</param>
        /// <param name="dateTo">The date to.</param>
        /// <returns></returns>
        private bool ScheduleDateExists(string operatorID, DateTime dateFrom, DateTime dateTo)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("    SELECT *");
            sql.Append("    FROM  [BookList]");
            sql.Append("    WHERE [OperatorID] = @OperatorID ");
            sql.Append("    AND [BookDateFrom] =@BookDateFrom ");
            sql.Append("    AND [BookDateTo] = @BookDateTo ");

            if (connection != null)
                using (SqlCommand cmd = new SqlCommand(sql.ToString(), connection, transaction))
                {
                    cmd.CommandText = sql.ToString();
                    cmd.CommandType = CommandType.Text;
                    cmd.Transaction = transaction;
                    cmd.Parameters.AddWithValue("@OperatorID", operatorID);
                    cmd.Parameters.AddWithValue("@BookDateFrom", dateFrom);
                    cmd.Parameters.AddWithValue("@BookDateTo", dateTo);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        return reader.HasRows;
                    }
                }
            return false;
        }


        /// <summary>
        /// Inserts the schedule.
        /// </summary>
        /// <param name="operatorID">The operator ID.</param>
        /// <param name="dateFrom">The date Bookfrom.</param>
        /// <param name="dateTo">The date Bookto.</param>
        /// <returns></returns>
        private bool InsertSchedule(string operatorID,DateTime dateFrom,DateTime dateTo)
        {
              StringBuilder sql = new StringBuilder();
                sql.Append("   INSERT INTO  [BookList] ");
                sql.Append(" (  [OperatorID] ");
                //sql.Append(",   [AccountID] ");
                sql.Append(",   [BookDateFrom] ");
                sql.Append(",   [BookDateTo] ");
                sql.Append(",   [UpdateTime]) ");
                sql.Append("    VALUES ");
                sql.Append("(   @OperatorID ");
                //sql.Append(",   @AccountID ");
                sql.Append(",   @BookDateFrom ");
                sql.Append(",   @BookDateTo ");
                sql.Append(",   @UpdateTime) ");
                sql.Append("    ; ");
                sql.Append("    INSERT INTO  [BookListHistory] ");
                sql.Append("    ([OperatorID] ");
                //sql.Append("    ,[AccountID] ");
                sql.Append("    ,[BookDateFrom] ");
                sql.Append("    ,[BookDateTo] ");
                sql.Append("    ,[UpdateTime]) ");
                sql.Append("    SELECT [OperatorID] ");
                //sql.Append("    ,[AccountID] ");
                sql.Append("    ,[BookDateFrom] ");
                sql.Append("    ,[BookDateTo] ");
                sql.Append("    ,[UpdateTime] ");
                sql.Append("    FROM  [BookList] ");
                sql.Append("    WHERE [OperatorID] = @OperatorID ");
                sql.Append("    AND [BookDateFrom] =@BookDateFrom ");
                sql.Append("    AND [BookDateTo] = @BookDateTo ");
                sql.Append("    ; ");

            if (transaction != null)
                using (SqlCommand cmd = new SqlCommand(sql.ToString(), connection, transaction))
                {
                    cmd.CommandText = sql.ToString();
                    cmd.CommandType = CommandType.Text;
                    cmd.Transaction = transaction;

                    cmd.Parameters.AddWithValue("@OperatorID", operatorID);
                    //cmd.Parameters.AddWithValue("@AccountID", accountID);
                    cmd.Parameters.AddWithValue("@BookDateFrom", dateFrom);
                    cmd.Parameters.AddWithValue("@BookDateTo", dateTo);
                    cmd.Parameters.AddWithValue("@UpdateTime", DateTime.Now);
                    return (cmd.ExecuteNonQuery() == 1);
                }
            return false;
        }


        /// <summary>
        /// Deletes the schedule.
        /// </summary>
        /// <param name="operatorID">The operator ID.</param>
        /// <param name="dateFrom">The date from.</param>
        /// <param name="dateTo">The date to.</param>
        /// <returns></returns>
        private bool DeleteSchedule(string operatorID, DateTime dateFrom, DateTime dateTo)
        {
            StringBuilder sql = new StringBuilder();
       
            sql.Append("    DELETE ");
            sql.Append("    FROM  [BookList]");
            sql.Append("    WHERE [OperatorID] = @OperatorID ");
            sql.Append("    AND [BookDateFrom] =@BookDateFrom ");
            sql.Append("    AND [BookDateTo] = @BookDateTo ");

            if (transaction != null)
                using (SqlCommand cmd = new SqlCommand(sql.ToString(), connection, transaction))
                {
                    cmd.CommandText = sql.ToString();
                    cmd.CommandType = CommandType.Text;
                    cmd.Transaction = transaction;
                    cmd.Parameters.AddWithValue("@OperatorID", operatorID);
                    cmd.Parameters.AddWithValue("@BookDateFrom", dateFrom);
                    cmd.Parameters.AddWithValue("@BookDateTo", dateTo);

                    return (cmd.ExecuteNonQuery() >= 1);
                }
            return false;
        } 


        /// <summary>
        /// Gets the book setting.
        /// </summary>
        /// <returns></returns>
        public List<BookSetting> GetBookSetting()
        {
            List<BookSetting> bookSettings = null;

            StringBuilder sql = new StringBuilder();
            sql.Append("    SELECT  [Seqno] ");
            sql.Append("           ,[Describe] ");
            sql.Append("            ,[BookDateFrom] ");
            sql.Append("            ,[BookDateTo] ");
            sql.Append("    FROM [BookSetting]");

            if (connection != null)
                using (SqlCommand cmd = new SqlCommand(sql.ToString(), connection, transaction))
                {
                    cmd.CommandText = sql.ToString();
                    cmd.CommandType = CommandType.Text;
                    cmd.Transaction = transaction;
                

                    using (SqlDataReader reader = cmd. ExecuteReader())
                    {
                        while ( reader.Read() )
                        {
                            if (bookSettings == null)
                                bookSettings = new List<BookSetting>();

                            bookSettings.Add(
                                new BookSetting
                                    {
                                        Seqno = reader["Seqno"] ==DBNull.Value?0:Convert.ToInt32( reader["Seqno"]),
                                        Describe = reader["Describe"] == DBNull.Value ? string.Empty : reader["Describe"].ToString() ,
                                        BookFrom = DateTime.ParseExact( reader["BookDateFrom"].ToString() ,"HH:mm:ss",null),
                                        BookTo = DateTime.ParseExact(reader["BookDateTo"].ToString(), "HH:mm:ss", null),
                                    }
                                );
                        }

                        reader.Close();
                    }
                }

            return bookSettings;
        }


        /// <summary>
        /// Gets the book config .
        /// </summary>
        /// <returns></returns>
        public int GetBookConfigII()
        {

            int bookDuration = 0;
            StringBuilder sql = new StringBuilder();
            sql.Append("    SELECT [BookDuration] FROM [BookConfig]");

            if (connection != null)
                using (SqlCommand cmd = new SqlCommand(sql.ToString(), connection, transaction))
                {
                    cmd.CommandText = sql.ToString();
                    cmd.CommandType = CommandType.Text;
                    cmd.Transaction = transaction;


                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            bookDuration = reader["BookDuration"] == DBNull.Value ? 0 : Convert.ToInt32(reader["BookDuration"]);
                        }

                        reader.Close();
                    }
                }

            return bookDuration;
        }


        /// <summary>
        /// Gets the next work time II.
        /// </summary>
        /// <param name="operatorID">The operator ID.</param>
        /// <param name="bookDate">The book date.</param>
        /// <returns></returns>
        public BookRecord GetNextWorkTimeII(string operatorID, DateTime bookDate)
        {
            BookRecord bookRecord = null;

            StringBuilder sql = new StringBuilder();
            sql.Append("    SELECT TOP 1 [BookDateFrom] ,[BookDateTo]  ");
            sql.Append("      FROM [BookList]     ");
            sql.Append("    WHERE  OperatorID = @OperatorID    ");
            sql.Append("    AND  [BookDateFrom]  >= @BookData    ");
            sql.Append("    ORDER BY  [BookDateFrom] ASC    ");

            if (connection != null)
                using (SqlCommand cmd = new SqlCommand(sql.ToString(), connection, transaction))
                {
                    cmd.CommandText = sql.ToString();
                    cmd.CommandType = CommandType.Text;
                    cmd.Transaction = transaction; 
                    cmd.Parameters.AddWithValue("@BookData", bookDate);
                    cmd.Parameters.AddWithValue("@OperatorID", operatorID);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            if (bookRecord == null)
                                bookRecord = new BookRecord() ;

                            bookRecord =
                                new BookRecord
                                    { 
                                        BookDateFrom = Convert.ToDateTime(reader["BookDateFrom"].ToString()),
                                        BookDateTo = Convert.ToDateTime(reader["BookDateTo"].ToString()),
                                       
                                    };

                        }

                        reader.Close();
                    }
                }

            return bookRecord;
        }

        /// <summary>
        /// Gets the book list.
        /// </summary>
        /// <param name="operatorID">The operator  ID.</param>
        /// <param name="bookDate">The book data.</param>
        /// <returns></returns>
        public List<BookRecord> GetBookListII(string operatorID, DateTime bookDate)
        {
            List<BookRecord> bookList = null;

            StringBuilder sql = new StringBuilder();
            sql.Append("    SELECT  [BookDateFrom]     ");
            sql.Append("         ,[BookDateTo]     ");
            sql.Append("      FROM [BookList]     ");
            sql.Append("    WHERE  OperatorID = @OperatorID    ");
            sql.Append("    AND  CAST ([BookDateFrom] AS DATE) = @BookData    ");
            sql.Append("    ORDER BY  [BookDateFrom]     ");

            if (connection != null)
                using (SqlCommand cmd = new SqlCommand(sql.ToString(), connection, transaction))
                {
                    cmd.CommandText = sql.ToString();
                    cmd.CommandType = CommandType.Text;
                    cmd.Transaction = transaction;
                    cmd.Parameters.Add("@BookData", SqlDbType.Date);
                    cmd.Parameters["@BookData"].Value = bookDate.Date;

                    cmd.Parameters.AddWithValue("@OperatorID", operatorID);
                    
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            if (bookList == null)
                                bookList = new List<BookRecord>();

                            bookList.Add(
                                new BookRecord
                                    {
                                        //OperatorID =
                                        //    reader["OperatorID"] == DBNull.Value
                                        //        ? string.Empty
                                        //        : reader["OperatorID"].ToString(),
                                        BookDateFrom = Convert.ToDateTime(reader["BookDateFrom"].ToString()),
                                        BookDateTo = Convert.ToDateTime(reader["BookDateTo"].ToString()),
                            //DateTime.ParseExact(reader["BookDateFrom"].ToString(), "HH:mm:ss", null),
                                }
                                );
                        }

                        reader.Close();
                    }
                }

            return bookList;
        }


        /// <summary>
        /// Gets the book list.
        /// </summary>
        /// <param name="operatorID">The operator ID.</param>
        /// <param name="bookDateFrom">The book date from.</param>
        /// <param name="bookDateTo">The book date to.</param>
        /// <returns></returns>
        public List<BookRecord> GetBookListII(string operatorID, DateTime bookDateFrom,DateTime bookDateTo)
        {
            List<BookRecord> bookList = null;

            StringBuilder sql = new StringBuilder();
            sql.Append("    SELECT  [BookDateFrom]     ");
            sql.Append("         ,[BookDateTo]     ");
            sql.Append("      FROM [BookList]     ");
            sql.Append("    WHERE  OperatorID = @OperatorID    ");
            sql.Append("    AND   [BookDateFrom] BETWEEN @BookDataFrom AND  @BookDataTo  ");


            if (connection != null)
                using (SqlCommand cmd = new SqlCommand(sql.ToString(), connection, transaction))
                {
                    cmd.CommandText = sql.ToString();
                    cmd.CommandType = CommandType.Text;
                    cmd.Transaction = transaction;
                    //cmd.Parameters.Add("@BookData", SqlDbType.Date);
                    cmd.Parameters.AddWithValue("@BookDataFrom", bookDateFrom);
                    cmd.Parameters.AddWithValue("@BookDataTo", bookDateTo);
                    cmd.Parameters.AddWithValue("@OperatorID", operatorID);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            if (bookList == null)
                                bookList = new List<BookRecord>();

                            bookList.Add(
                                new BookRecord
                                {
                                   
                                    BookDateFrom = Convert.ToDateTime(reader["BookDateFrom"].ToString()),
                                    BookDateTo = Convert.ToDateTime(reader["BookDateTo"].ToString()),
                                  
                                }
                                );
                        }

                        reader.Close();
                    }
                }

            return bookList;
        }


        /// <summary>
        /// Gets the book days.
        /// </summary>
        /// <param name="operatorID">The operator ID.</param>
        /// <param name="dateFrom">The date from.</param>
        /// <param name="dateTo">The date to.</param>
        /// <returns></returns>
        public List<DateTime> GetBookDaysII(string operatorID, DateTime dateFrom,DateTime dateTo)
        {
            List<DateTime> bookDays = null;

            StringBuilder sql = new StringBuilder();
            sql.Append("    SELECT DISTINCT CAST ([BookDateFrom] AS DATE) [BookDate]     ");
            sql.Append("      FROM [BookList]     ");
            sql.Append("    WHERE  OperatorID = @OperatorID    ");
            sql.Append("    AND  [BookDateFrom] BETWEEN  @DateFrom AND @DateTo  ");


            if (connection != null)
                using (SqlCommand cmd = new SqlCommand(sql.ToString(), connection, transaction))
                {
                    cmd.CommandText = sql.ToString();
                    cmd.CommandType = CommandType.Text;
                    cmd.Transaction = transaction; 
                    cmd.Parameters.AddWithValue("@OperatorID", operatorID);
                    cmd.Parameters.AddWithValue("@DateFrom", dateFrom);
                    cmd.Parameters.AddWithValue("@DateTo", dateTo);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            if (bookDays == null)
                                bookDays = new List<DateTime>();

                            bookDays.Add(  Convert.ToDateTime(reader["BookDate"].ToString() ));
                        }

                        reader.Close();
                    }
                }

            return bookDays;
        }


        /// <summary>
        /// Inserts the operator action.
        /// </summary>
        /// <param name="operatorID">The operator ID.</param>
        /// <param name="actionType">Type of the action.</param>
        /// <returns></returns>
        public bool InsertOperatorAction(string operatorID,string actionType)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("   INSERT INTO [OperatorAction] ");
            sql.Append("              ([OperatorID] ");
            sql.Append("              ,[ActionDate] ");
            sql.Append("              ,[ActionType]) ");
            sql.Append("        VALUES ");
            sql.Append("              (@OperatorID ");
            sql.Append("              ,@ActionDate ");
            sql.Append("              ,@ActionType) ");

           

            if (transaction != null)
                using (SqlCommand cmd = new SqlCommand(sql.ToString(), connection, transaction))
                {
                    cmd.CommandText = sql.ToString();
                    cmd.CommandType = CommandType.Text;
                    cmd.Transaction = transaction;

                    cmd.Parameters.AddWithValue("@OperatorID", operatorID); 
                    cmd.Parameters.AddWithValue("@ActionDate", DateTime.Now);
                    cmd.Parameters.AddWithValue("@ActionType", actionType); 
                    return (cmd.ExecuteNonQuery() == 1);
                }
            return false;
        }


        #region

        private SqlConnection connection;
        private SqlTransaction transaction;

        public void BeginTransaction()
        {
            if (transaction == null)
            {
                transaction = connection.BeginTransaction();
            }
        }

        public void CommitTransaction()
        {
            if (transaction != null)
            {
                transaction.Commit();
                transaction = null;
            }
        }

        public void RollbackTransaction()
        {
            if (transaction != null)
            {
                transaction.Rollback();
                transaction = null;
            }
        }

        public void Close()
        {
            RollbackTransaction();
            if (connection != null)
            {
                connection.Close();
                connection = null;
            }
        }
        #endregion 


        #region Disposable / Finalize

        private bool disposed;

        ~DataBase()
        {
            Dispose(false);
        }

        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }

        protected virtual void Dispose(bool disposing)
        {
            if (!disposed)
            {
                Close();
            }
            disposed = true;
        }


        #endregion

    }
}

 
